﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Citi.PTSConsolidation.Domain.Utils
{
    public static class SqlHelper
    {
        public static void CopyExcelToSql(string sourceConnectionString, string sourceQuery,
            string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping, string sheetName = null, bool copyAllSheets = false)
        {
            using (var sourceConnection = new OleDbConnection(sourceConnectionString))
            {
                sourceConnection.Open();

                if (copyAllSheets)
                {
                    var sheets = sourceConnection.GetSchema("TABLES").Rows.Cast<DataRow>()
                        .Select(x => x[2].ToString().StartsWith(sheetName) && !x[2].ToString().StartsWith("'") ? x[2].ToString() : x[2].ToString().Substring(1));

                    sheets = sheets.Where(x => string.IsNullOrWhiteSpace(sheetName) || x.StartsWith(sheetName))
                        .Select(x => x.EndsWith("$") ? x : x.Substring(0, x.Length - 1));

                    foreach (var sheet in sheets)
                        CopyOledbToSql(string.Format(sourceQuery, sheet), destinationTable, destinationConnectionString, columnMapping, sourceConnection);
                }
                else
                    CopyOledbToSql(string.Format(sourceQuery, sheetName), destinationTable, destinationConnectionString, columnMapping, sourceConnection);
            }

            //CopyTo(destinationTable, destinationConnectionString, columnMapping, reader);
        }

        private static void CopyOledbToSql(string sourceQuery, string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping, OleDbConnection sourceConnection)
        {
            var command = new OleDbCommand(sourceQuery, sourceConnection);
            command.CommandTimeout = 20000;
            IDataReader reader = command.ExecuteReader();
            CopyTo(destinationTable, destinationConnectionString, columnMapping, reader);
        }

        public static void CopyDataFromAnotherDatabase(string sourceConnectionString, string sourceQuery,
            string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping)
        {
            IDataReader reader;

            using (var sourceConnection = new SqlConnection(sourceConnectionString))
            {
                var command = new SqlCommand(sourceQuery, sourceConnection) { CommandTimeout = 12000000 };
                sourceConnection.Open();
                reader = command.ExecuteReader();
                CopyTo(destinationTable, destinationConnectionString, columnMapping, reader);
            }

            //CopyTo(destinationTable, destinationConnectionString, columnMapping, reader);
        }

        public static void CopyTo(string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping, DataTable dataTable)
        {
            //using (var destinationConnection = new SqlConnection(destinationConnectionString))
            //{
            //    destinationConnection.Open();

            using (var bulkCopy = new SqlBulkCopy(destinationConnectionString))
            {
                bulkCopy.BatchSize = 5000;
                bulkCopy.BulkCopyTimeout = 12000000;
                bulkCopy.NotifyAfter = 10000;
                bulkCopy.DestinationTableName = destinationTable;
                //bulkCopy.SqlRowsCopied += bulkCopy_SqlRowsCopied;

                foreach (var column in columnMapping)
                    bulkCopy.ColumnMappings.Add(column.Key, column.Value);

                bulkCopy.WriteToServer(dataTable);
            }
            //}
        }

        //static void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        //{
        //    e.
        //}

        public static void CopyTo(string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping, IDataReader dataReader)
        {
            //using (var destinationConnection = new SqlConnection(destinationConnectionString))
            //{
            //    destinationConnection.Open();

            using (var bulkCopy = new SqlBulkCopy(destinationConnectionString))
            {
                bulkCopy.BatchSize = 5000;
                bulkCopy.NotifyAfter = 10000;
                bulkCopy.BulkCopyTimeout = 12000000;
                bulkCopy.DestinationTableName = destinationTable;

                foreach (var column in columnMapping)
                    bulkCopy.ColumnMappings.Add(column.Key.Split(new[] { '.' }).Last(), column.Value);

                bulkCopy.WriteToServer(dataReader);
            }
            //}
        }

        //private static string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PTSDatabase"].ConnectionString;

        public static DataTable ExecuteReader(string query, string connectionString)
        {
            var connection = new SqlConnection(connectionString);
            //connection.ConnectionTimeout = 600;
            connection.Open();

            var dataTable = new DataTable();

            try
            {
                using (connection)
                {
                    var command = new SqlCommand(query, connection) { CommandTimeout = 20000 };
                    //var reader = command.ExecuteReader();

                    var adapter = new SqlDataAdapter(command);
                    adapter.Fill(dataTable);
                }
            }
            catch (Exception)
            {
                connection.Close();
                throw;
            }

            return dataTable;
        }

        public static void ExecuteNonQuery(string query, string connectionString)
        {
            var connection = new SqlConnection(connectionString);
            //connection.ConnectionTimeout = 600;
            connection.Open();

            try
            {
                using (connection)
                {
                    var command = new SqlCommand(query, connection) { CommandTimeout = 20000 };
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
    }
}
